import tkinter as tk
from tkinter import messagebox,filedialog
from 数据库的连接与使用 import Connect
from tkinter import ttk
import pandas as pd
search_keys = {"员工":["员工编号","员工姓名","员工密码","员工级别","员工电话","员工工资","备注"] ,
               "供应商": ["供应商编号","供应商名称","供应商简称","地址","公司电话","邮件","联系人电话","备注"],
               "商品": ["商品编号","商品名称","商品单价","供应商编号","商品简介","备注"],
               "采购主表":["采购清单号","员工编号","采购数量","采购总价","采购时间","备注"],
               "采购明细":["采购明细号","采购清单号","商品编号","采购数量","商品单价","商品总价","备注"]}  # 表：主码

class Mass_import:
    def __init__(self,master,name,columns,table):
        self.child = tk.Toplevel(master)
        self.child.title(name)
        self.columns_values = columns
        self.table = table
        # 导入界面
        self.insert_view = tk.Frame(self.child)  # 添加面纸
        self.insert_view.pack()  # 绑定
        self.new_data = []
        self.mass_import()
    def mass_import(self):
        # 创建一个按钮用于选择文件
        tk.Button(self.insert_view, text='选择文件', command=self.load_file).grid(row=0, column=0, pady=10)

    def load_file(self):
        self.file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
        if self.file_path:
            try:
                df = pd.read_excel(self.file_path)
                self.show_columns_mapping(df.columns)
            except Exception as e:
                messagebox.showerror("错误", f"文件读取失败：{e}")

    def show_columns_mapping(self, file_columns):
        mapping_view = tk.Toplevel(self.child)
        mapping_view.title("列名映射")

        for i, col in enumerate(file_columns, start=1):
            tk.Label(mapping_view, text=col).grid(row=i, column=0, padx=5, pady=5)
            tk.Label(mapping_view, text=self.columns_values[i-1]).grid(row=i, column=1, padx=5, pady=5)

        tk.Button(mapping_view, text="开始导入", command=self.perform_import).grid(row=len(file_columns) + 1, columnspan=2, pady=10)

    def perform_import(self):
        try:
            if not self.file_path:
                raise ValueError("未选择文件！")

            df = pd.read_excel(self.file_path)

            if list(df.columns) != self.columns_values:
                raise ValueError("文件列与数据库表列不匹配！")

            for index, row in df.iterrows():
                data = {db_col: row[file_col] for file_col, db_col in zip(df.columns, self.columns_values)}
                db = Connect()
                db.insert_with_trigger_check(self.table, data)

            messagebox.showinfo("提示", "数据导入成功！")
            self.child.destroy()

        except Exception as e:
            messagebox.showerror("错误", f"数据导入失败：{e}")


class Export:
    def __init__(self, master, table_name):
        self.master = master
        self.table_name = table_name
        self.export_csv()

    def export_csv(self):
        try:
            # 连接数据库获取员工数据
            conn = Connect()  # 假设这是连接数据库的方法
            employee_data = conn.select(self.table_name)  # 假设这是从数据库中获取员工数据的方法

            # 将数据转换为DataFrame
            df = pd.DataFrame(employee_data, columns=search_keys[self.table_name])

            # 弹出文件选择对话框，获取保存路径
            file_path = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV files", "*.csv")])

            if file_path:
                # 导出数据到CSV文件
                df.to_csv(file_path, index=False, encoding='utf-8-sig')
                messagebox.showinfo("成功", f"成功导出 {self.table_name} 数据到 {file_path}")

        except Exception as e:
            messagebox.showerror("错误", f"导出失败: {e}")
class Insert_Frame:
    #创建子窗口
    def __init__(self,master,name,columns,table): #name窗口的名字  columns是属性组   table表名
        self.child=tk.Toplevel(master)
        self.child.title(name)
        self.columns_values = columns
        self.table=table
        # 插入界面
        self.insert_view = tk.Frame(self.child)  # 添加面纸
        self.insert_view.pack()  # 绑定
        self.new_data = []
        self.insert()
    def insert(self):#框架
        #输入框
        for i, col in enumerate(self.columns_values):    #获取可迭代对象
            tk.Label(self.insert_view, text=col).grid(row=i, column=0,pady=5)
            entry_var = tk.StringVar()
            tk.Entry(self.insert_view, textvariable=entry_var).grid(row=i, column=5)
            self.new_data.append(entry_var)
        #按钮
        tk.Button(self.insert_view,text='确定',command=self.check_input).grid(row=len(self.columns_values),column=1,pady=15)
    def check_input(self):
        # 检查输入框内容
        data = {col: var.get() for col, var in zip(self.columns_values, self.new_data)}
        if all(data.values()): # 所有输入框都有内容
            try:
                # 连接数据库,传data
                conn=Connect()
                flag,message=conn.insert_with_trigger_check(self.table,data)
                #print(data)
                if flag:
                    messagebox.showinfo("notice", "插入成功！")
                    self.child.destroy()
                else:
                    messagebox.showerror("错误",message)

            except Exception as e:
                messagebox.showerror("notice",f"数据库操作失败：{e}")
        else:
            # 有输入框为空
            messagebox.showwarning("notice", "操作失败：所有字段必须填写！")
class Delete_Frame:
    # 创建子窗口
    def __init__(self, master, name,table):  # name窗口的名字
        self.child = tk.Toplevel(master)
        self.child.geometry('400x400')
        self.child.title(name)
        self.table=table
        # 删除界面
        self.delete_view = tk.Frame(self.child)  # 添加面纸
        self.delete_view.pack()  # 绑定
        self.delete()
    def delete(self):
        #布局
        self.delete_id = tk.StringVar()
        tk.Label(self.delete_view, text='请输入要删除的数据编号').grid(row=0, column=0)
        tk.Entry(self.delete_view, textvariable=self.delete_id).grid(row=0, column=1)
        tk.Button(self.delete_view, text='确定',command=self.check_input).grid(row=1, column=0,pady=20)
    def check_input(self):
        delete_id=self.delete_id.get()
        if delete_id:
            # 连接数据库,传data
            conn = Connect()
            #检查数据是否存在
            flag,delete_data=conn.search_data(self.table,delete_id)
            columns=search_keys[self.table]
            if flag:
                #隐藏当前界面,显示数据
                self.delete_view.destroy()
                #创建删除确认界面
                self.delete_data_frame(delete_id,delete_data,columns)


    def delete_data_frame(self,delete_id,delete_data,columns):
        self.confirm_delete_view = tk.Frame(self.child)
        self.confirm_delete_view.pack()
        # 显示表
        self.data_show = ttk.Treeview(self.confirm_delete_view, show='headings', columns=columns)
        # 列宽&对齐方式
        for col in columns:
            self.data_show.column(col, width=100, anchor='center')
            self.data_show.heading(col, text=col)
        #index=1
        # 填充数据
        self.data_show.pack(fill=tk.BOTH, expand=True)
        for index,row in enumerate(delete_data):
            self.data_show.insert('',index+1,values=(row))

        # 确认删除按钮
        button_frame=tk.Frame(self.confirm_delete_view)
        button_frame.pack(pady=10)
        tk.Button(button_frame, text='确认删除', command=lambda: self.delete_data(delete_id)).pack()

    def delete_data(self,delete_id):
        try:
            conn=Connect()
            flag,message=conn.delete(self.table,delete_id)
            if flag:
                messagebox.showinfo('notice', '删除成功！')
            else:
                messagebox.showinfo('notice',message=message)
                self.child.destroy()
        except Exception as e:
            messagebox.showerror('notice', f"删除失败：{e}")
class Updata_Frame:
    def __init__(self, master, name,columns,table):  # name窗口的名字
        self.child = tk.Toplevel(master)
        self.child.title(name)
        self.columns_values=columns
        self.table=table
        # 插入界面
        self.update_frame = tk.Frame(self.child)  # 添加面纸
        self.update_frame.pack()  # 绑定
        self.insert()
    def insert(self):
        # 输入框
        self.new_data = {}
        for i, col in enumerate(self.columns_values):  # 获取可迭代对象
            tk.Label(self.update_frame, text=col).grid(row=i, column=0, pady=5)
            j= tk.StringVar()
            tk.Entry(self.update_frame, textvariable=j).grid(row=i, column=1)
            self.new_data[col]=j
        # 按钮
        tk.Button(self.update_frame, text='修改', command=self.change_user).grid(row=len(self.columns_values), column=1,pady=15)

    def change_user(self):
        data = {key: var.get() for key, var in self.new_data.items()}
        conn=Connect()
        flag,result = conn.search_data(self.table,self.new_data[self.columns_values[0]].get())
        if flag:
            conn.update(self.table,data,self.new_data[self.columns_values[0]].get())
            messagebox.showinfo("notice", "修改成功!")
            self.child.destroy()
        else:
            messagebox.showwarning("notice", "用户不存在!")

class Check_Frame():
    def __init__(self, master, table_name):
        self.child=tk.Toplevel(master)
        self.child.geometry('1300x400')
        self.child.title(table_name)
        self.table_name = table_name

        self.show()

    def show(self):

        # 界面
        input_frame = ttk.Frame(self.child, padding="10")
        input_frame.pack(padx=10, pady=10, fill=tk.BOTH, expand=True)

        # 输入框
        ttk.Label(input_frame, text="输入最大采购数量：").grid(row=0, column=0, padx=5, pady=5)
        self.max_number_entry = ttk.Entry(input_frame, width=10)
        self.max_number_entry.grid(row=0, column=1, padx=5, pady=5)

        # 按键
        show_data_btn = ttk.Button(input_frame, text="显示数据", command=self.show_data)
        show_data_btn.grid(row=0, column=2, padx=5, pady=5)

        # 填充
        self.tree = ttk.Treeview(input_frame, columns=search_keys["采购主表"], show="headings")
        for col in search_keys["采购主表"]:
            self.tree.heading(col, text=col)
        self.tree.grid(row=1, column=0, columnspan=3, padx=5, pady=5, sticky="nsew")


    def show_data(self):
        try:
            max_number = int(self.max_number_entry.get())
        except ValueError:
            messagebox.showerror("错误", "请输入有效的数字！")
            return

        conn = Connect()
        flag,data=conn.check(max_number,self.table_name)        #flag是标志，data是数据
        if flag:
            # 清空现有的数据
            for row in self.tree.get_children():
                self.tree.delete(row)

            # 显示查询到的数据
            for row in data:
                self.tree.insert("", "end", values=row)
        else:
            messagebox.showerror("notice", message=data)
